In smaller organizations, iMIS Analytics might be the only data warehouse providing you with business intelligence. In larger organizations with several major databases besides your iMIS database, the data warehousing experts in your organization can implement Analytics as a data mart in the bus architecture of a larger enterprise data warehouse solution (per the Kimball model). Describing this type of data mart implementation is beyond the scope of this document, because such implementations are highly customized solutions. A data mart implementation is possible, however, because the Analytics data warehouse uses dimensional models and a star schema, and because the Analytics OLAP cube has been designed around conformed dimensions and conformed measures (facts). Your data warehousing experts can rely on documentation for Microsoft Analysis Services or any related third-party documentation to be applicable to the design of the Analytics data warehouse and analysis database.
Whether Analytics is implemented as your only data warehouse or as a data mart, there are major decisions that you must make when implementing Analytics:
■ Should the product be installed in a two-tier or single-tier server configuration?
■ Which of your custom iMIS demographics (custom fields) should be added to Analytics?
■ Which type of slowly-changing dimension (SCD) should you use for each custom demographic?
Server configurations
Analytics server components can be implemented on a single tier with your iMIS database, or spread across two hosts in a two-tier configuration:
■ Two-tier: The Analytics data warehouse and analysis database are located together on one SQL Server host (the Analytics host), and your iMIS database is located on a different SQL Server host (the iMIS host). Both the Analytics host and the iMIS host must be members of the same domain.
■ Single-tier: The Analytics data warehouse and analysis database are located on the same SQL Server host that contains your iMIS database. In this configuration, the Analytics host is also the iMIS host.
The single-tier configuration is the easiest to install and use, and generally won't affect performance of your iMIS database unless you have many people constantly using the analysis database. The following information about the three databases involved in an Analytics implementation can help you determine whether a two-tier or single-tier configuration is best for your organization:
■ iMIS database
current data stored in relational tables
Your iMIS database contains some historical records of things like donations, purchases, etc. Much of the information in your iMIS database, however, is always current data. For example, if a product description or customer's address changes, the old product description or the old customer address is not saved in the iMIS database.
■ data warehouse
historical data stored in dimensional tables using a star schema
The Analytics data warehouse is a special database that contains a copy of all your iMIS data. The data warehouse does not use relational tables, but instead structures your data in a dimensional model using a star schema. Because much of the copied data comprises slowly changing dimensions that preserve history (Type 2 SCDs), the data warehouse will grow over time, so space requirements for growth are a factor in determining whether to implement a two-tier or single-tier configuration. However, since the data warehouse is used only during the extraction, transform, and load (ETL) process during off-peak hours, the performance impact on the iMIS host in a single-tier configuration is negligible.
■ analysis database - OLAP cube
OLAP cube derived from data warehouse contents, using conformed dimensions and conformed measures (facts)
The Analytics analysis database contains a copy of the information from the data warehouse, transformed into a cube in an OLAP database. As with the data warehouse, the analysis database will grow over time, so space requirements for growth are a factor in determining whether to implement a two-tier or single-tier configuration. Unlike the data warehouse, this database might be used heavily during peak hours of the business day. However, OLAP viewers such as ProClarity Professional do not generate heavy SQL activity, so the performance impact on the iMIS host in a single-tier configuration is minimal.
Custom iMIS demographics
The Customers feature of iMIS has a feature called the iMIS Customizer, which enables you to create custom tables for use in custom window tabs that capture your own demographic information about each customer. With some limitations, you can configure your implementation of Analytics to include your custom demographic information about each customer in the data warehouse and analysis database. The following limitations apply to custom iMIS demographics:
■ Fields from custom tables that are defined to allow multiple table rows for a single customer ID (multi-instance custom tables) cannot be included in Analytics.
■ Fields from single-instance custom tables are added as attributes to the Customer Ext Dim dimension.
Examining these limitations in more detail can help you plan how to configure Analytics to track custom iMIS demographics (see Configuring Analytics to track custom iMIS demographics).
A multi-instance custom table is one for which the Multiple Instances Allowed checkbox was selected when creating the custom table with the iMIS Customizer. In a multi-instance custom table, you can have multiple records for the same customer ID. For example, you might want to send out annual surveys to each of your customers and track the survey results in a custom table that is viewable on a custom Surveys tab in the Manage Customers window. In this scenario, the Surveys tab for a specific customer would show one row for each year's survey results.
In a single-instance custom table, by contrast, there would never be multiple records for the same customer ID. For example, you might have created a custom table that tracks general demographic information for customers, such as their spouse's name and the school they attended. In this scenario, there would never be more than one row needed per customer ID to store the demographic information.
Because multi-instance custom tables can have more than one record per customer ID, Analytics cannot determine which of those multiple records to display as attributes for a specific customer when you are examining the detail information for that customer in an OLAP viewer such as ProClarity Professional. Therefore, only fields from single-instance custom tables can be included in your implementation of Analytics.
The other limitation of custom tables is that the fields from these tables are essentially extended attributes of a specific customer ID, instead of being a category of business information that could be considered a dimension member in its own right. Therefore, the custom fields that you add to Analytics are not treated as members (levels) in a dimension that you can directly graph on a chart in OLAP viewers such as ProClarity Professional. Instead, you can view the custom fields only as attributes of a specific customer, once you have drilled-down far enough into a view to display specific customers.
Slowly changing dimensions
The most difficult decision to make when configuring Analytics to track your custom demographics is which type of slowly changing dimension (SCD) to use for each demographic. In a data warehouse, the measures (facts) change very rapidly, but by comparison, the data in your dimensions change slowly. For example, a custom demographic such as staff size is likely to change over time for a given customer.
Analytics supports two types of slowly changing dimensions, as well as giving you the option to not use a slowly changing dimension for demographics that never change over time. There are two questions you should consider for each demographic that you want to track in Analytics:
■ Is this demographic a value that might change over time?
□ If the answer is no, then you should configure the demographic to not use a slowly changing dimension.
□ If the answer is yes, then you should consider the following question to determine which type of slowly changing dimension to use.
■ Do I need to preserve a history of the different values of this demographic as it changes over time, or do I care only about the most current value of this demographic?
□ If you need to preserve the historical values of a demographic, you should use a Type 2 SCD.
□ If you need only the current value of a demographic, you should use a Type 1 SCD.
The following information about slowly changing dimensions can also help you determine which SCD type to use for each custom field:
■ Type 2
Preserves history
When changes occur to a Type 2 SCD, a new record is added with the new attribute information, preserving records of the old attribute information. Type 2 SCDs track history very efficiently because they create new records only when the attribute information changes. However, because Type 2 changes add new records, they can significantly increase the size of your data warehouse.
■ Type 1
Overwrites history
When changes occur to a Type 1 SCD, no new records are created. Instead, the change overwrites the existing attribute with new information. The old information is lost forever.
■ Type 0
Use no SCD
A Type 0 SCD is a special value used only in Analytics to indicate that the custom demographic is not a slowly changing dimension. The demographic is a value that will never change for a given customer. The advantage of using a Type 0 SCD for custom demographics that never change is that the data warehouse requires less overhead to maintain that demographic.